# -*- coding: utf-8 -*-
# @FileName : 1.py
# @Author   : YinXiang
# @Time     : 2025/9/4 16:35
import pandas as pd
import numpy as np
import openpyxl
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
from openpyxl.utils import get_column_letter
import matplotlib.pyplot as plt
from matplotlib import rcParams

# 设置中文字体
rcParams['font.sans-serif'] = ['SimHei']  # 用来正常显示中文标签
rcParams['axes.unicode_minus'] = False  # 用来正常显示负号

# 读取Excel文件
file_path = "测试题目.xlsx"
df = pd.read_excel(file_path, sheet_name="Sheet1")

# 数据预处理
df = df.dropna(subset=['物料编码'])  # 移除空行
df['数量ABC'] = pd.to_numeric(df['数量ABC'], errors='coerce')  # 确保数量为数值类型
df = df.sort_values('数量ABC', ascending=False)  # 按数量降序排列

# 计算总数量和占比
total_quantity = df['数量ABC'].sum()
df['占比'] = df['数量ABC'] / total_quantity

# 计算累积占比
df['累积占比'] = df['占比'].cumsum()


# 确定ABC分类
def assign_abc(cumulative_percentage):
    if cumulative_percentage <= 0.6:
        return 'A'
    elif cumulative_percentage <= 0.85:
        return 'B'
    else:
        return 'C'


df['ABC分类'] = df['累积占比'].apply(assign_abc)

# 创建新的DataFrame用于输出
output_df = df[['物料编码', '数量ABC', '占比', '累积占比', 'ABC分类']].copy()
output_df.columns = ['物料编码', '数量/ADC', '占比', '累计占比', '数量/ADC']

# 格式化百分比列
output_df['占比'] = output_df['占比'].apply(lambda x: f"{x:.1%}")
output_df['累计占比'] = output_df['累计占比'].apply(lambda x: f"{x:.0%}")

# 保存到新的Excel文件
output_file = "ABC分析结果.xlsx"
with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
    output_df.to_excel(writer, sheet_name='ABC分析', index=False)

    # 获取工作簿和工作表对象
    workbook = writer.book
    worksheet = writer.sheets['ABC分析']

    # 设置列宽
    column_widths = [12, 12, 10, 12, 12]
    for i, width in enumerate(column_widths, 1):
        worksheet.column_dimensions[get_column_letter(i)].width = width

    # 设置标题行样式
    header_fill = PatternFill(start_color="DDEBF7", end_color="DDEBF7", fill_type="solid")
    header_font = Font(bold=True, color="000000")
    thin_border = Border(left=Side(style='thin'),
                         right=Side(style='thin'),
                         top=Side(style='thin'),
                         bottom=Side(style='thin'))

    for cell in worksheet[1]:
        cell.fill = header_fill
        cell.font = header_font
        cell.border = thin_border
        cell.alignment = Alignment(horizontal='center', vertical='center')

    # 设置数据行样式
    for row in worksheet.iter_rows(min_row=2, max_row=worksheet.max_row, min_col=1, max_col=worksheet.max_column):
        for cell in row:
            cell.border = thin_border
            if cell.column in [3, 4]:  # 百分比列
                cell.alignment = Alignment(horizontal='center')
            else:
                cell.alignment = Alignment(horizontal='center', vertical='center')

            # 根据ABC分类设置背景色
            if cell.column == 5:  # ABC分类列
                if cell.value == 'A':
                    cell.fill = PatternFill(start_color="E2EFDA", end_color="E2EFDA", fill_type="solid")
                elif cell.value == 'B':
                    cell.fill = PatternFill(start_color="FFF2CC", end_color="FFF2CC", fill_type="solid")
                elif cell.value == 'C':
                    cell.fill = PatternFill(start_color="FCE4D6", end_color="FCE4D6", fill_type="solid")

# 创建可视化图表
plt.figure(figsize=(10, 6))

# 绘制帕累托图
fig, ax1 = plt.subplots(figsize=(12, 6))

# 条形图（数量）
bars = ax1.bar(range(len(df)), df['数量ABC'], color='steelblue', alpha=0.7)
ax1.set_xlabel('物料编码')
ax1.set_ylabel('数量', color='steelblue')
ax1.tick_params(axis='y', labelcolor='steelblue')

# 折线图（累积占比）
ax2 = ax1.twinx()
ax2.plot(range(len(df)), df['累积占比'] * 100, color='red', marker='o', linewidth=2)
ax2.set_ylabel('累积占比 (%)', color='red')
ax2.tick_params(axis='y', labelcolor='red')
ax2.set_ylim(0, 100)

# 添加ABC分类区域
ax2.axhline(y=60, color='green', linestyle='--', alpha=0.7)
ax2.axhline(y=85, color='orange', linestyle='--', alpha=0.7)
ax2.text(len(df) * 0.05, 62, 'A类 (0-60%)', color='green', fontsize=12)
ax2.text(len(df) * 0.05, 87, 'B类 (60-85%)', color='orange', fontsize=12)
ax2.text(len(df) * 0.05, 92, 'C类 (85-100%)', color='red', fontsize=12)

plt.title('物料ABC分析帕累托图')
plt.xticks(range(len(df)), df['物料编码'], rotation=45)
plt.tight_layout()
plt.savefig('ABC分析帕累托图.png', dpi=300)

print(f"处理完成！结果已保存到 {output_file}")
print(f"可视化图表已保存为 ABC分析帕累托图.png")

# 显示处理后的数据
print("\n处理后的数据：")
print(output_df.to_string(index=False))